var mysql = require('mysql2');
//promise支持
var Q = require('q');
var fs = require("fs")
let os = require('os');
let dbconfig = {
	host: process.env.DB_HOST,
	user: process.env.DB_USER,
	password: process.env.DB_PASSWORD,
	port: process.env.DB_PORT,
	database: process.env.DB_NAME,
	multipleStatements: true,
	charset: "utf8mb4",
	waitForConnections: 100,
	connectionLimit: 1000,
	queueLimit: 1000

}

console.log(dbconfig)

//数据库连接池
var pool = mysql.createPool(dbconfig);
//设置连接睡眠时间 set global interactive_timeout=300;set global wait_timeout=300;
/*
show status like 'Threads%'
show processlist
SHOW GLOBAL VARIABLES LIKE '%timeout%'
*/

//普通查询
var query = function(sql, callback) {
	pool.getConnection(function(conn_err, conn) {
		if (conn_err) {
			callback(conn_err, null, null);
		} else {
			conn.query(sql, function(query_err, rows, fields) {

				//pool.releaseConnection(conn);
				conn.release();
				callback(query_err, rows, fields);
			});
		}
	});
};

var getTableJson = (sql, page, pagesize, para) => {
	var def = Q.defer();
	pool.getConnection((conn_err, conn) => {
		if (conn_err) {
			def.reject(null);
		} else {
			if (!page) {
				page = 1;
			}
			if (!pagesize) {
				pagesize = 20;
			}
			let nsql = sql + ' limit ' + (page - 1) * pagesize + "," + pagesize + ";";

			conn.query(nsql, para ? para : [], function(query_err, rows, fields) {
				if (rows) {
					let _sql = sql.toLowerCase();

					let tjsql = "select count(*) _totalcount  from   (select 1  " + _sql.substr(_sql.indexOf("from"))+" ) as _tmp";

				
					conn.query(tjsql, para ? para : [], (a, b, c) => {
						
						//pool.releaseConnection(conn);
						conn.release();
						if (b) {
							b = JSON.parse(JSON.stringify(b));
							var data = JSON.parse(JSON.stringify(rows));
							//console.log(rows)
							let obj = {
								page: {
									count: b.length>0?b[0]._totalcount:0,
									per_page: pagesize,
									current_page: page,
									total_page: Math.ceil((b.length>0?b[0]._totalcount:0) / pagesize)
								},
								data: data

							}

							def.resolve(obj);
						} else {
							def.resolve({
								code: 500,
								msg: a
							});
						}

					})


				} else {
					//pool.releaseConnection(conn);
					conn.release();
					def.resolve({
						code: 500,
						msg: query_err
					});

				}

			});
		}
	});
	return def.promise;
}

var getTableJsonAll = (sql, para) => {
	var def = Q.defer();

	pool.getConnection((conn_err, conn) => {
		if (conn_err) {
			console.log("conn_err");
			console.log(conn_err);
			
			def.reject(null);
		} else {

			let nsql = sql;

			conn.query(sql, para ? para : [], function(query_err, rows, fields) {
				//pool.releaseConnection(conn);
				conn.release();
				if (rows) {
					var data = JSON.parse(JSON.stringify(rows));
					def.resolve(data);

				} else {
					def.resolve({
						code: 500,
						msg: query_err
					});

				}

			});
		}
	});
	return def.promise;
}
/**  
 * @description 在resolve中返回，空值返回0    
 */
var getRow00 = (sql, para) => {
	var def = Q.defer();
	pool.getConnection((conn_err, conn) => {
		if (conn_err) {

			def.reject(null);
		} else {
			conn.query(sql, para, function(query_err, rows, fields) {


				//pool.releaseConnection(conn);
				conn.release();
				if (rows) {
					var data = JSON.parse(JSON.stringify(rows));

					if (data.length > 0) {
						for (let k in data[0]) {
							def.resolve(data[0][k]);
							break;
						}

					} else {
						def.resolve(0);
					}

				} else {
					//console.log(query_err)

					def.reject(null);
				}
			});
		}
	});
	return def.promise;
}

var getOneRow = (sql, para) => {
	var def = Q.defer();
	pool.getConnection((conn_err, conn) => {
		if (conn_err) {
			//console.log("conn_err")
			//console.log(conn_err)
			def.reject(null);
		} else {
			conn.query(sql, para, function(query_err, rows, fields) {

				//pool.releaseConnection(conn);
				conn.release();
				if (rows) {

					var data = JSON.parse(JSON.stringify(rows));
					if (data.length > 0) {
						def.resolve(data[0]);
					} else {
						def.reject({});
					}

				} else {
					def.reject(query_err);
				}
			});
		}
	});
	return def.promise;
}


var getFirstRow = (sql, para) => {
	var def = Q.defer();
	pool.getConnection((conn_err, conn) => {
		if (conn_err) {
			console.log("conn_err")
			console.log(conn_err)
			def.reject(null);
		} else {
			conn.query(sql, para, function(query_err, rows, fields) {

				//pool.releaseConnection(conn);
				conn.release();
				if (rows) {

					var data = JSON.parse(JSON.stringify(rows));
					if (data.length > 0) {
						def.resolve(data[0]);
					} else {
						def.resolve(null);
					}

				} else {
					def.reject(query_err);
				}
			});
		}
	});
	return def.promise;
}

var excateSql = (sql, para, opt) => {
	var def = Q.defer();
	if (sql) {
		pool.getConnection((conn_err, conn) => {
			conn.multipleStatements = true;
			if (conn_err) {
				def.reject(null);
			} else {
				conn.query(sql, para, function(query_err, rows, fields) {

					//pool.releaseConnection(conn);
					conn.release();
					if (!query_err) {
						def.resolve(rows);
						if(!opt||!opt.notlog){
							log.write("执行sql成功", {
								sql: sql,
								para: para,
								msg: rows,
								opt: opt,
								pmid:opt&&opt.pmid?opt.pmid:0
							})
						}
						
						
						


					} else {
						console.log(query_err)
						def.reject(query_err);
					}

				})
			}
		});
	}else{
		def.resolve({code:200,msg:"nosql"});
	}
	return def.promise;
}
var excateInsertSql = (sql, para,opt) => {
	var def = Q.defer();
	pool.getConnection((conn_err, conn) => {
		if (conn_err) {
			//console.log(conn_err)
			def.reject(null);
		} else {


			conn.query(sql, para, function(query_err, rows, fields) {

				//pool.releaseConnection(conn);
				conn.release();

				if (!query_err) {

					def.resolve(rows.insertId);
					if(!opt||!opt.notlog){
						log.write("执行sql成功", {
							sql: sql,
							para: para,
							msg: rows
						})
					}
					
				} else {

					def.reject(query_err);
				}

			})
		}
	});
	return def.promise;
}

var execTransection = (sqlArr) => {
	return new Promise((resolve, reject) => {
		var promiseArr = [];
		pool.getConnection(function (err, connection) {
			if (err) {
				return reject(err)
			}
			connection.beginTransaction(err => {
				if (err) {
					return reject('开启事务失败')
				}
				// 将所有需要执行的sql封装为数组
				promiseArr = sqlArr.map(({ sql, values }) => {
					return new Promise((resolve, reject) => {
						connection.query(sql, values, (e, rows, fields) => {
							e ? reject(e) : resolve({ rows, success: true })
							if (!e) {
								log.write("执行sql成功", {
									sql: sql,
									para: values,
									msg: rows
								})
							}
						})
					})
				})
				// Promise调用所有sql，一旦出错，回滚，否则，提交事务并释放链接
				Promise.all(promiseArr).then(res => {
					connection.commit((error) => {
						if (error) {
							console.log('事务提交失败')
							reject(error)
						}
					})
					connection.release()  // 释放链接
					resolve(res)
				}).catch(err => {
					connection.rollback(() => {
						console.log('数据操作回滚')
					})
					reject(err)
				})
			})
		});
	})
}

module.exports = {
	query,
	getTableJson,
	getOneRow,
	excateSql,
	getRow00,
	excateInsertSql,
	getTableJsonAll,
	getFirstRow,
	execTransection,
}
